﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Prototype
{
    public class Payment
    {
        //private static string connString = ConfigurationManager.ConnectionStrings[0].ToString();

        private static string connString = @"Data Source=.\SQLEXPRESS;Initial Catalog=Hotel System;Integrated Security=True";

        private PaymentType paymentType;
        private DateTime date;
        private string reason;
        private decimal price;

        public string PaymentType
        {
            get
            { return paymentType.Name; }
        }

        public DateTime Date
        {
            get { return date; }
            set { date = value; }
        }

        public string Reason
        {
            get { return reason; }
        }

        public decimal Price
        {
            get { return price; }
            set { price = value; }
        }

        private Payment(string payingTypeName, DateTime date, string reason, decimal price)
        {
            this.paymentType = new PaymentType(payingTypeName);
            this.date = date;
            this.reason = reason;
            this.price = price;
        }

        public static void InsertPayment(string paymentType, string reason, DateTime sDate, decimal price)
        {
            DateTime date = new DateTime(sDate.Year, sDate.Month, sDate.Day);
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            try
            {
                String command = "INSERT INTO Payment(PaymentTypeID, Reason, Date, Sum)" +
                     " VALUES (@PaymentTypeID, @Reason, @Date, @Sum)";

                SqlCommand cmdInsertPayment = new SqlCommand(command, conn);

                #region parameters

                SqlParameter paramPaymentTypeID = new SqlParameter("@PaymentTypeID", SqlDbType.Int);

                SqlCommand cmdGetPaymentTypeID = new SqlCommand("SELECT PaymentTypeID FROM PaymentType WHERE Name=@Name", conn);
                SqlParameter paramName = new SqlParameter("@Name", SqlDbType.NVarChar);
                paramName.Value = paymentType;
                cmdGetPaymentTypeID.Parameters.Add(paramName);
                int paymentTypeID = (int)cmdGetPaymentTypeID.ExecuteScalar();

                paramPaymentTypeID.Value = paymentTypeID;
                cmdInsertPayment.Parameters.Add(paramPaymentTypeID);

                SqlParameter paramReason = new SqlParameter("@Reason", SqlDbType.NVarChar);
                paramReason.Value = reason;
                cmdInsertPayment.Parameters.Add(paramReason);

                SqlParameter paramDate = new SqlParameter("@Date", SqlDbType.DateTime);
                paramDate.Value = date;
                cmdInsertPayment.Parameters.Add(paramDate);

                SqlParameter paramSum = new SqlParameter("@Sum", SqlDbType.Decimal);
                paramSum.Value = price;
                cmdInsertPayment.Parameters.Add(paramSum);
                #endregion

                cmdInsertPayment.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public static Payment[] GetPayments(DateTime fromDate, DateTime toDate, bool isIncome)
        {
            List<Payment> allPayments = new List<Payment>();
            SqlConnection conn = new SqlConnection(connString);
            string commandString = "SELECT t.Name, t.PaymentTypeID, p.PaymentTypeID, p.Date, p.Reason, p.Sum FROM Payment p" +
                " JOIN PaymentType t on p.PaymentTypeID=t.PaymentTypeID" +
                " WHERE t.Name=@PaymentType and (p.Date > @FromDate and p.Date < @ToDate)";
           
            conn.Open();
            try
            {
                SqlCommand cmdGetAllPayments = new SqlCommand(commandString, conn);

                SqlParameter paramPaymentType = new SqlParameter("@PaymentType", SqlDbType.NVarChar);
                if (isIncome)
                    paramPaymentType.Value = "Income";
                else
                    paramPaymentType.Value = "Expense";
                cmdGetAllPayments.Parameters.Add(paramPaymentType);
                
                SqlParameter paramFromDate = new SqlParameter("@FromDate", SqlDbType.DateTime);
                paramFromDate.Value = fromDate;
                cmdGetAllPayments.Parameters.Add(paramFromDate);

                SqlParameter paramToDate = new SqlParameter("@ToDate", SqlDbType.DateTime);
                paramToDate.Value = toDate;
                cmdGetAllPayments.Parameters.Add(paramToDate);
                
                SqlDataReader reader = cmdGetAllPayments.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        Payment payment = new Payment(reader["Name"].ToString(), (DateTime)reader["Date"],
                            reader["Reason"].ToString(), (decimal)reader["Sum"]);
                        allPayments.Add(payment);
                    }
                }
                return allPayments.ToArray();
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
